﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataLayer
{
    public class DataAccess
    {
        private SqlDataAdapter DataAdapter;
        private SqlCommand Command;

        public DataAccess()
        {
           // conn = new SqlConnection(ApplicationSettings.ConnectionString);
        }

        public DataTable Query(string queryName, SqlParameter[] sparam = null)
        {
            DataTable ret = new DataTable();
            using (SqlConnection conn = new SqlConnection(ApplicationSettings.ConnectionString))
            {
                try
                {
                    conn.Open();
                    if (sparam == null)
                    {
                        //Câu lệnh truy vấn trực tiếp
                        if (queryName.Contains(' '))
                        {
                            DataAdapter = new SqlDataAdapter(queryName, conn);
                        }
                        //Thủ tục không tham số
                        else
                        {
                            Command = new SqlCommand(queryName, conn);
                            Command.CommandType = CommandType.StoredProcedure;
                            DataAdapter = new SqlDataAdapter(Command);
                        }
                    }
                    //Thủ tục có tham số
                    else
                    {
                        Command = new SqlCommand(queryName, conn);
                        Command.CommandType = CommandType.StoredProcedure;
                        foreach (SqlParameter p in sparam)
                        {
                            Command.Parameters.Add(p);
                        }
                        DataAdapter = new SqlDataAdapter(Command);
                    }
                }
                catch
                {
                    throw;
                }
                DataAdapter.Fill(ret);
            }
            
            return ret;
        }

        public void NonQuery(string nonQueryName, SqlParameter[] spram = null)
        {
            using (SqlConnection conn = new SqlConnection(ApplicationSettings.ConnectionString))
            {
                try
                {
                    conn.Open();
                    Command = new SqlCommand(nonQueryName, conn);
                    if (spram == null)
                    {
                        if (nonQueryName.Contains(' '))
                        {
                            Command.CommandType = CommandType.Text;
                        }
                        else
                        {
                            Command.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    else
                    {
                        Command.CommandType = CommandType.StoredProcedure;
                        Command.Parameters.Clear();
                        foreach (SqlParameter p in spram)
                        {
                            Command.Parameters.Add(p);
                        }
                    }

                    Command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public List<string> GetParameter(string StoredProcedure)
        {
            List<string> param = new List<string>();
            using (SqlConnection conn = new SqlConnection(ApplicationSettings.ConnectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(StoredProcedure, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    SqlCommandBuilder.DeriveParameters(cmd);  //Kiem tra cac tham so
                    cmd.Parameters.RemoveAt(0);
                    foreach (SqlParameter sp in cmd.Parameters)
                    {
                        param.Add(sp.ParameterName);
                    }
                }
                catch
                {
                    throw;
                }
            }
            return param;
        }


        internal static DataTable Query(string p, SqlParameter sqlParameter)
        {
            throw new NotImplementedException();
        }
    }
}
